﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Reflection;

namespace PageAdmin.Utils.ORM
{
    /// <summary>
    /// 涉及数据库转换类,参数转换，数据转换等等，主要用于PageAdmin.Utils.ORM内部调用
    /// </summary>
    internal class DBConvertHelper
    {
        //private static Dictionary<string, DataColumnCollection> _DicDataColumnCollection = new Dictionary<string, DataColumnCollection>();
        //private static object _Lock = new object();

        #region 参数转换
        /// <summary>
        /// 
        /// </summary>
        /// <param name="paramters"></param>
        /// <param name="sql"></param>
        /// <param name="dataBaseType"></param>
        /// <returns></returns>
        public static IDbDataParameter[] ConvertParameter(DbKeyValueList paramters, string sql, DataBaseType dataBaseType)
        {
            if (paramters == null) { return null; }
            List<IDbDataParameter> dbDataParameter = new List<IDbDataParameter>();
            foreach (var parameterInfo in paramters)
            {
                IDbDataParameter dataParamter = GetDbParameter(parameterInfo, dataBaseType);
                dbDataParameter.Add(dataParamter);
            }
            return dbDataParameter.ToArray();
        }

        /// <summary>
        /// 获取字段参数信息
        /// </summary>
        /// <param name="parameterInfo"></param>
        /// <param name="dataBaseType"></param>
        /// <returns></returns>
        private static DbParameter GetDbParameter(DbParameterInfo parameterInfo, DataBaseType dataBaseType)
        {
            string paramName = parameterInfo.Key;
            object value = parameterInfo.Value;
            if(value==null)
            {
                value ="";
            }
            // paramName中必须带有@符号开头
            if (paramName.Substring(0, 1) != "@")
            {
                paramName = "@" + paramName;
            }
            DbParameter dbParameter = null;
            string dataType = value.GetType().Name;
            switch (dataType)
            {
                case "decimal":
                case "Decimal":
                    if (dataBaseType == DataBaseType.Access)
                    {
                        dbParameter = new OleDbParameter(paramName, OleDbType.Decimal)
                        {
                            Value = value
                        };
                    }
                    else
                    {
                        dbParameter = new SqlParameter(paramName, SqlDbType.Decimal)
                        {
                            Value = value
                        };
                    }
                    break;

                case "Int64":
                case "Long":
                    if (dataBaseType == DataBaseType.Access)
                    {
                        dbParameter = new OleDbParameter(paramName, OleDbType.BigInt)
                        {
                            Value = value
                        };
                    }
                    else
                    {
                        dbParameter = new SqlParameter(paramName, SqlDbType.BigInt)
                        {
                            Value = value
                        };
                    }
                    break;

                case "int":
                case "Int32":
                    if (dataBaseType == DataBaseType.Access)
                    {
                        dbParameter = new OleDbParameter(paramName, OleDbType.Integer)
                        {
                            Value = value
                        };
                    }
                    else
                    {
                        dbParameter = new SqlParameter(paramName, SqlDbType.Int)
                        {
                            Value = value
                        };
                    }
                    break;

                case "short":
                case "Int16":
                    if (dataBaseType == DataBaseType.Access)
                    {
                        dbParameter = new OleDbParameter(paramName, OleDbType.SmallInt)
                        {
                            Value = value
                        };
                    }
                    else
                    {
                        dbParameter = new SqlParameter(paramName, SqlDbType.SmallInt)
                        {
                            Value = value
                        };
                    }
                    break;

                case "SByte":
                case "Byte":
                case "byte":
                    if (dataBaseType == DataBaseType.Access)
                    {
                        dbParameter = new OleDbParameter(paramName, OleDbType.TinyInt)
                        {
                            Value = value
                        };

                    }
                    else
                    {
                        dbParameter = new SqlParameter(paramName, SqlDbType.TinyInt)
                        {
                            Value = value
                        };
                    }
                    break;

                case "double":
                case "Double":
                    if (dataBaseType == DataBaseType.Access)
                    {
                        dbParameter = new OleDbParameter(paramName, OleDbType.Double)
                        {
                            Value = value
                        };
                    }
                    else
                    {
                        dbParameter = new SqlParameter(paramName, SqlDbType.Float)
                        {
                            Value = value
                        };
                    }
                    break;

                case "Single":
                case "single":
                case "float":
                case "Float":
                    if (dataBaseType == DataBaseType.Access)
                    {
                        dbParameter = new OleDbParameter(paramName, OleDbType.Single)
                        {
                            Value = value
                        };
                    }
                    else
                    {
                        dbParameter = new SqlParameter(paramName, SqlDbType.Float)
                        {
                            Value = value
                        };
                    }
                    break;

                case "datetime":
                case "DateTime":
                    if (dataBaseType == DataBaseType.Access)
                    {
                        dbParameter = new OleDbParameter(paramName, OleDbType.Date)
                        {
                            Value = value.ToString()
                        };
                    }
                    else
                    {
                        dbParameter = new SqlParameter(paramName, SqlDbType.DateTime)
                        {
                            Value = value
                        };
                    }
                    break;


                //case "nvarchar":
                //    if (dataBaseType == DataBaseType.Access)
                //    {
                //        if (size > 255)
                //        {
                //            size = -1;
                //        }
                //        dbParameter = new OleDbParameter(paramName, OleDbType.LongVarChar)
                //        {
                //            Value = value,
                //            Size = size
                //        };
                //    }
                //    else
                //    {
                //        if (size>4000)
                //        {
                //            size = -1;
                //        }
                //        dbParameter = new SqlParameter(paramName, SqlDbType.NVarChar)
                //        {
                //            Value = value,
                //            Size = size
                //        };
                //    }
                //    break;

                //case "varchar":
                //    if (dataBaseType == DataBaseType.Access)
                //    {
                //        if (size >255)
                //        {
                //            size = -1;
                //        }
                //        dbParameter = new OleDbParameter(paramName,OleDbType.LongVarChar)
                //        {
                //            Value = value,
                //            Size = size
                //        };
                //    }
                //    else
                //    {
                //        if(size>8000)
                //        {
                //            size = -1;
                //        }
                //        dbParameter = new SqlParameter(paramName, SqlDbType.VarChar)
                //        {
                //            Value = value,
                //            Size = size
                //        };
                //    }
                //    break;


                //case "nchar":
                //    if (dataBaseType == DataBaseType.Access)
                //    {
                //        if (size > 255)
                //        {
                //            size = -1;
                //        }
                //        dbParameter = new OleDbParameter(paramName, OleDbType.LongVarChar)
                //        {
                //            Value = value,
                //            Size = size
                //        };
                //    }
                //    else
                //    {
                //        if (size > 4000)
                //        {
                //            size = 4000;
                //        }
                //        dbParameter = new SqlParameter(paramName, SqlDbType.NChar)
                //        {
                //            Value = value,
                //            Size = size
                //        };
                //    }
                //    break;

                //case "char":
                //    if (dataBaseType == DataBaseType.Access)
                //    {
                //        if (size > 255)
                //        {
                //            size = -1;
                //        }
                //        dbParameter = new OleDbParameter(paramName, OleDbType.LongVarChar)
                //        {
                //            Value = value,
                //            Size = size
                //        };
                //    }
                //    else
                //    {
                //        if (size > 8000)
                //        {
                //            size = 8000;
                //        }
                //        dbParameter = new SqlParameter(paramName, SqlDbType.Char)
                //        {
                //            Value = value,
                //            Size = size
                //        };
                //    }
                //    break;

                //case "ntext":
                //    if (dataBaseType == DataBaseType.Access)
                //    {
                //        dbParameter = new OleDbParameter(paramName, OleDbType.LongVarChar)
                //        {
                //            Value = value,
                //        };
                //    }
                //    else
                //    {
                //        dbParameter = new SqlParameter(paramName, SqlDbType.NText)
                //        {
                //            Value = value
                //        };
                //    }
                //    break;

                //case "text":
                //    if (dataBaseType == DataBaseType.Access)
                //    {
                //        dbParameter = new OleDbParameter(paramName, OleDbType.LongVarChar)
                //        {
                //            Value = value,
                //        };
                //    }
                //    else
                //    {
                //        dbParameter = new SqlParameter(paramName, SqlDbType.Text)
                //        {
                //            Value = value
                //        };
                //    }
                //    break;

                default:
                    dbParameter = new SqlParameter(paramName, value);
                    if(parameterInfo.Size>0)
                    {
                        dbParameter.Size = parameterInfo.Size;
                    }
                    break;
            }

            return dbParameter;
        }

        #endregion


        #region 数据转换
        /// <summary>
        /// idatareader一行转ExpandoObject对象
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static ExpandoObject IDataReaderToDynamic(IDataReader dr)
        {
            dynamic d = new ExpandoObject();
            var dictionary = d as IDictionary<string, object>;
            for (int i = 0; i < dr.FieldCount; i++)
            {
                dictionary.Add(BasicHelper.FirstCharUpper(dr.GetName(i)), FormatFieldValue(dr.GetValue(i), ((DbDataReader)dr).GetFieldType(i).Name));
            }
            return d;
        }

        /// <summary>
        ///  idatareader一行转实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <param name="type"></param>
        /// <param name="propertyInfos"></param>
        /// <returns></returns>
        public static T IDataReaderToClass<T>(IDataReader dr, Type type, PropertyInfo[] propertyInfos) where T : class
        {
            T obj = (T)Activator.CreateInstance(type); //反射创建实例

            for (int i = 0; i < dr.FieldCount; i++)
            {
                string fieldName = dr.GetName(i);
                string typeName = ((DbDataReader)dr).GetFieldType(i).Name;
                object value = dr.GetValue(i);
                if (value == DBNull.Value) { value = null; }
                var propertyInfo = propertyInfos.Where(c => c.Name.Equals(fieldName, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                if (propertyInfo != null)
                {
                    if (value != null && propertyInfo.CanWrite)
                    {
                        if (propertyInfo.PropertyType.IsValueType || propertyInfo.PropertyType == typeof(string))
                        {
                            propertyInfo.SetValue(obj, value);
                        }
                    }
                }
            }
            return obj;
        }



        /// <summary>
        /// IDataReader一行转动态类型
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static dynamic ConvertToDynamic(IDataReader dr)
        {
            dynamic dynamicObj = null;
            try
            {
                if (dr != null && !dr.IsClosed)
                {
                    if (dr.Read())
                    {
                        dynamicObj = IDataReaderToDynamic(dr);
                    }
                    dr.Close();
                }
            }
            catch
            {
                dr.Close();
            }
            return dynamicObj;

        }


        /// <summary>
        /// IDataReader一行转实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static T ConvertToModel<T>(IDataReader dr) where T : class
        {
            T genericObj = null;
            try
            {
                if (dr != null && !dr.IsClosed)
                {
                    if (dr.Read())
                    {
                        Type type = typeof(T); //获取类型
                        var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
                        genericObj = IDataReaderToClass<T>(dr, type, properties);
                    }
                    dr.Close();
                }
            }
            catch
            {
                dr.Close();
            }
            return genericObj;
        }


        /// <summary>
        /// IDataReader转ExpandoObject集合
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static IEnumerable<ExpandoObject> ConvertToList(IDataReader dr)
        {
            ICollection<ExpandoObject> list = new List<ExpandoObject>();
            try
            {
                if (dr != null && !dr.IsClosed)
                {
                    while (dr.Read())
                    {
                        //2018-2-24，在事务，Viewabg等尝尽中延迟会导致很多问题，取消
                        //yield return IDataReaderToDynamic(dr); //延时加载,赋值给viewbag时候不ToList()会导致无数据

                        //2018-2-24,重新启用
                        list.Add(IDataReaderToDynamic(dr));
                    }
                    dr.Close();
                }

            }
            catch
            {
                dr.Close();
            }
            return list;
        }


        /// <summary>
        ///  IDataReader转实体集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static IEnumerable<T> ConvertToList<T>(IDataReader dr) where T : class
        {
            ICollection<T> list = new List<T>();
            try
            {
                if (dr != null && !dr.IsClosed)
                {
                    Type type = typeof(T); //获取类型
                    var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
                    //IDictionary<string, PropertyInfo> propertyInfos = new Dictionary<string, PropertyInfo>();
                    //foreach (var property in properties)
                    //{
                    //   propertyInfos.Add(property.Name.ToLower(), property);
                    //}
                    while (dr.Read())
                    {
                        list.Add(IDataReaderToClass<T>(dr, type, properties));
                        //yield return IDataReaderToClass<T>(dr, type, properties);
                    }
                    dr.Close();
                }
            }
            catch
            {
                dr.Close();
            }
            return list;
        }

        #endregion

        /// <summary>
        /// 对数据库中读出来的数据数据进行格式化,这样就不会再出现dbNUll这样的数据,主要用于配合dynamic集合
        /// </summary>
        /// <param name="value"></param>
        /// <param name="dataType"></param>
        /// <returns></returns>
        private static object FormatFieldValue(object value, string dataType)
        {
            if (value == DBNull.Value)
            {
                value = "";
            }
            if (value == null)
            {
                value = "";
            }
            bool result;
            switch (dataType.ToLower())
            {
                case "int32":
                case "int":
                    int intValue = 0;
                    result = int.TryParse(value.ToString(), out intValue);
                    return intValue;
                //break;

                case "short":
                case "int16":
                    short shortValue = 0;
                    result = short.TryParse(value.ToString(), out shortValue);
                    return shortValue;
                //break;

                case "long":
                case "int64":
                    short longValue = 0;
                    result = short.TryParse(value.ToString(), out longValue);
                    return longValue;
                    //break;
                    ;
                case "tiyint":
                case "byte":
                    byte byteValue = 0;
                    result = byte.TryParse(value.ToString(), out byteValue);
                    return byteValue;
                //break;

                case "float":
                    float floatValue = 0;
                    result = float.TryParse(value.ToString(), out floatValue);
                    return floatValue;
                //break;


                case "double":
                    double doubleValue = 0;
                    result = double.TryParse(value.ToString(), out doubleValue);
                    return doubleValue;
                //break;

                case "decimal":
                    decimal decimalValue = 0;
                    result = decimal.TryParse(value.ToString(), out decimalValue);
                    return decimalValue;
                //break;

                case "bool":
                case "boolean":
                    bool boolValue = false;
                    result = bool.TryParse(value.ToString(), out boolValue);
                    return boolValue;
                //break;

                case "datetime":
                    DateTime dateValue;
                    result = DateTime.TryParse(value.ToString(), out dateValue);
                    if (!result)
                    {
                        dateValue = DateTime.Now;
                    }
                    return dateValue;
                //break;

                default:
                    return value;
                    //break;
            }

        }

    }

}
